package com.hbnu.dao.impl;

import com.hbnu.dao.UserDao;
import com.hbnu.pojo.DormBuild;
import com.hbnu.pojo.User;
import com.hbnu.util.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author chendikai
 * @date 2024-11-06 上午 10:38
 */
public class UserDaoImpl implements UserDao {
    @Override
    public User findUserByStuCodeAndPassword(String stuCode, String password) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtils.getConnection();

            String sql = "select * from tb_user where stu_code = ? and password = ? and disabled = 0";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, stuCode);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                User user = new User();   // 实体类对象

                // 将查询出来的数据封装到User对象中
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setStuCode(resultSet.getString("stu_code"));
                user.setDormCode(resultSet.getString("dorm_code"));
                user.setSex(resultSet.getString("sex"));
                user.setTel(resultSet.getString("tel"));
                user.setDormBuildId(resultSet.getInt("dormBuildId"));
                user.setRoleId(resultSet.getInt("role_id"));
                user.setCreateUserId(resultSet.getInt("create_user_id"));
                user.setDisabled(resultSet.getInt("disabled"));

                return user;   // 返回查出来的对象
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }

        return null;
    }

    @Override
    public List<User> findManager(String searchType, String keyword) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<User> userList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();

            String temp = "";
            if (null != searchType && null != keyword && !"".equals(keyword)) {
                if ("name".equals(searchType)) {
                    temp = " and name like '%" + keyword + "%'";
                }
                if ("sex".equals(searchType)) {
                    temp = " and sex like '%" + keyword + "%'";
                }
                if ("tel".equals(searchType)) {
                    temp = " and tel like '%" + keyword + "%'";
                }
            }

            String sql = "select * from tb_user where role_id = 1";

            if (!"".equals(temp)) {
                sql += temp;
            }
            preparedStatement = connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                User user = new User();

                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setSex(resultSet.getString("sex"));
                user.setTel(resultSet.getString("tel"));
                user.setDisabled(resultSet.getInt("disabled"));

                userList.add(user);

            }

            return userList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }
        return null;
    }

    @Override
    public List<DormBuild> findDormBuildsByUserId(Integer userId) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<DormBuild> dormBuilds = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();

            String sql = "SELECT tb_dormbuild.* FROM tb_manage_dormbuild LEFT JOIN tb_dormbuild ON " +
                    "tb_manage_dormbuild.dormBuild_id = tb_dormbuild.id " +
                    "WHERE tb_manage_dormbuild.user_id = ?";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1, userId);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                DormBuild dormBuild = new DormBuild();

                dormBuild.setId(resultSet.getInt("id"));
                dormBuild.setName(resultSet.getString("name"));
                dormBuild.setRemark(resultSet.getString("remark"));
                dormBuild.setDisabled(resultSet.getInt("disabled"));

                dormBuilds.add(dormBuild);
            }

            return dormBuilds;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 资源关闭
            DBUtils.close(resultSet, preparedStatement, connection);
        }

        return null;
    }
    @Override
    public List<User> findStudent(String sql) {
        // ① 获取连接（数据库地址  用户名 密码）
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;


        try {
            connection = DBUtils.getConnection();
            // ③ 获取集装箱或者说是车
            preparedStatement = connection.prepareStatement(sql);
            // ④执行SQL,获取执行后的结果,查询的结果封装在ResultSet
            rs = preparedStatement.executeQuery();
            // 因为查询出来的结果包括表头信息，所以要指针下移一行，看是否有查询出来的数据
            // 如有数据，就进入循环体，封装该行数据
            List<User> users = new ArrayList<User>();
            while (rs.next()) {
                User user = new User();
                // 每一行的数据封装在一个实体bean中，根据字段名获取字段值，注意该字段是什么类型，就get什么类型
                user.setId(rs.getInt("id"));
                user.setCreateUserId(rs.getInt("create_user_id"));
                user.setDisabled(rs.getInt("disabled"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setRoleId(rs.getInt("role_id"));
                user.setSex(rs.getString("sex"));
                user.setStuCode(rs.getString("stu_code"));
                user.setTel(rs.getString("tel"));
                user.setDormCode(rs.getString("dorm_code"));
                user.setDormBuildId(rs.getInt("dormBuildId"));
                DormBuild build = new DormBuild();
                build.setId(rs.getInt("dormBuildId"));
                build.setName(rs.getString("buildName"));
                build.setRemark(rs.getString("remark"));
                user.setDormBuild(build);

                users.add(user);
            }
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, preparedStatement, connection);
        }
        return null;
    }


    @Override
    public Integer findTotalNum(String sql) {
        // ① 获取连接（数据库地址  用户名 密码）
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            connection = DBUtils.getConnection();
            // ② 准备SQL语句
            // ③ 获取集装箱或者说是车
            preparedStatement = connection.prepareStatement(sql);
            // ④执行SQL,获取执行后的结果,查询的结果封装在ResultSet
            rs = preparedStatement.executeQuery();
            rs.next();
            return rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, preparedStatement, connection);
        }
        return null;
    }


    @Override
    public User findByStuCode(String stuCode) {
        // ① 获取连接（数据库地址  用户名 密码）
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            connection = DBUtils.getConnection();
            // ② 准备SQL语句
            String sql = "select * from tb_user where stu_code = ? ";
            // ③ 获取集装箱或者说是车
            preparedStatement = connection.prepareStatement(sql);
            // 索引从1开始
            preparedStatement.setString(1, stuCode);
            // ④执行SQL,获取执行后的结果,查询的结果封装在ResultSet
            rs = preparedStatement.executeQuery();
            // 因为查询出来的结果包括表头信息，所以要指针下移一行，看是否有查询出来的数据
            // 如有数据，就进入循环体，封装该行数据
            while (rs.next()) {
                User user = new User();
                // 每一行的数据封装在一个实体bean中，根据字段名获取字段值，注意该字段是什么类型，就get什么类型
                user.setId(rs.getInt("id"));
                user.setCreateUserId(rs.getInt("create_user_id"));
                user.setDisabled(rs.getInt("disabled"));
                user.setDormBuildId(rs.getInt("dormBuildId"));
                user.setDormCode(rs.getString("dorm_code"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setRoleId(rs.getInt("role_id"));
                user.setSex(rs.getString("sex"));
                user.setStuCode(rs.getString("stu_code"));
                user.setTel(rs.getString("tel"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, preparedStatement, connection);
        }
        return null;
    }
    @Override
    public void updateStudent(User user) {
        // ① 获取连接（数据库地址  用户名 密码）
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            // ② 准备SQL语句
            // IFNULL(参数1，参数2)函数，用于判断第一个表达式是否为NULL，如果为NULL，则返回第二个参数的值。如果不为NULL，就返回第一个参数的值

            String sql = "UPDATE tb_user SET name = ?,passwod=?,sex=?,tel=?,disabled= ?,stu_code=?,dorm_code=?,dormBuildId=? WHERE ";

            // ③ 获取集装箱或者说是车  Statement.RETURN_GENERATED_KEYS指定返回生成的注解
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getSex());
            preparedStatement.setString(4, user.getTel());
            preparedStatement.setInt(5, user.getDisabled());
            preparedStatement.setString(6, user.getStuCode());
            preparedStatement.setString(7, user.getDormCode());
            preparedStatement.setInt(8, user.getDormBuildId());
            preparedStatement.setInt(9, user.getId());
            // ④执行SQL,获取执行后的结果,查询的结果封装在ResultSet
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(null, preparedStatement, connection);
        }
    }

    @Override
    public void saveStudent(User user) {
        // ① 获取连接（数据库地址  用户名 密码）
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            // ② 准备SQL语句
            // IFNULL(参数1，参数2)函数，用于判断第一个表达式是否为NULL，如果为NULL，则返回第二个参数的值。如果不为NULL，就返回第一个参数的值
            String sql = "INSERT INTO tb_user(name,password,stu_code,dorm_code,sex,tel,dormBuildId,role_id,create_user_id) " + "VALUES(?,?,?,?,?,?,?,?,?)";
            // ③ 获取集装箱或者说是车  Statement.RETURN_GENERATED_KEYS指定返回生成的注解
            preparedStatement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getStuCode());
            preparedStatement.setString(4, user.getDormCode());
            preparedStatement.setString(5, user.getSex());
            preparedStatement.setString(6, user.getTel());
            preparedStatement.setInt(7, user.getDormBuildId());
            preparedStatement.setInt(8, user.getRoleId());
            preparedStatement.setInt(9, user.getCreateUserId());
            // ④执行SQL,获取执行后的结果,查询的结果封装在ResultSet



            preparedStatement.executeUpdate();
        /*resultSet = preparedStatement.getGeneratedKeys();
       resultSet.next();
       Integer id = resultSet.getInt(1);*/ } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(null, preparedStatement, connection);
        }
    }

    @Override
    public User findByUserIdAndManager(String sql) {
        // ① 获取连接（数据库地址  用户名 密码）
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            connection = DBUtils.getConnection();
            // ③ 获取集装箱或者说是车
            preparedStatement = connection.prepareStatement(sql);
            // ④执行SQL,获取执行后的结果,查询的结果封装在ResultSet
            rs = preparedStatement.executeQuery();
            // 因为查询出来的结果包括表头信息，所以要指针下移一行，看是否有查询出来的数据
            // 如有数据，就进入循环体，封装该行数据
            while (rs.next()) { User user = new User();
                // 每一行的数据封装在一个实体bean中，根据字段名获取字段值，注意该字段是什么类型，就get什么类型
                user.setId(rs.getInt("id"));
                user.setCreateUserId(rs.getInt("create_user_id"));
                user.setDisabled(rs.getInt("disabled"));
                user.setDormBuildId(rs.getInt("dormBuildId"));
                user.setDormCode(rs.getString("dorm_code"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setRoleId(rs.getInt("role_id"));
                user.setSex(rs.getString("sex"));
                user.setStuCode(rs.getString("stu_code"));
                user.setTel(rs.getString("tel"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, preparedStatement, connection);
        }
        return null;
    }

    @Override
    public User findById(int id) {
        return null;
    }
}




